import openpyxl
from QuickTools import tools_pmc

wb_kc = openpyxl.load_workbook(r"C:\Users\10352\Desktop\1.xlsx")
ws_kc = wb_kc.worksheets[0]
wb = openpyxl.load_workbook(
    r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\2，呆滞料管理\长库龄改制后消耗情况表.xlsx")
ws = wb.worksheets[0]


def main():
    ws.cell(i, 7).value = 0
    for j in range(2, ws_kc.max_row + 1):
        if int(ws.cell(i, 5).value) == int(ws_kc.cell(j, 1).value) and \
                ws.cell(i, 1).value <= tools_pmc.detect_date(ws_kc.cell(row=j, column=5).value, 1):
            ws.cell(i, 7).value += ws_kc.cell(j, 13).value
    ws.cell(i, 8).value = ws.cell(i, 4).value - ws.cell(i, 7).value
    if ws.cell(i, 8).value > 0:
        ws.cell(i, 9).value = "剩余未售出数量"
        ws.cell(i, 9).font = tools_pmc.font_red
    else:
        ws.cell(i, 9).value = "已全部售出"


for i in range(2, ws.max_row + 1):
    if ws.cell(i, 1).value is None:
        ws.cell(i, 1).value = tools_pmc.detect_date(ws.cell(i, 10).value[0:8], 3)
    ws.cell(i, 7).value = 0
    main()

ws = wb.worksheets[1]
for i in range(2, ws.max_row + 1):
    if ws.cell(i, 1).value is None:
        ws.cell(i, 1).value = tools_pmc.detect_date(ws.cell(i, 10).value, 3)
    main()

wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\2，呆滞料管理\长库龄改制后消耗情况表1.xlsx")
